set more off
clear all
cls

********************************************************************************
****************************OECD PRICES*****************************************
********************************************************************************

import delimited "original_data/prices/data_OECD.csv", clear

rename time year

keep if v6=="Index"

drop country subject measure v6 frequency v8 v10 unit* powercode* reference* flag*

rename ïlocation country
label variable country "country_code"

sort country v4 year




replace v4="CPI_all" if v4=="CPI: 01-12 - All items"
replace v4="CPI_recreation_total" if v4=="CPI: 09 - Recreation and culture"
replace v4="CPI_hh_items" if v4=="CPI: 05 - Furnishings, household equipment and routine household maintenance"
replace v4="CPI_all_harm" if v4=="HICP: All items"
replace v4="CPI_recreation_total_harm" if v4=="HICP: 09 - Recreation and culture"
replace v4="CPI_hh_items_harm" if v4=="HICP: 05 - Furnishings, household equipment and routine household maintenance"

keep if v4=="CPI_all" | v4=="CPI_recreation_total" | v4=="CPI_all_harm" | v4=="CPI_recreation_total_harm" | v4=="CPI_hh_items" | v4=="CPI_hh_items_harm"

reshape wide value, i(country year) j(v4) string

local list all recreation_total hh_items all_harm recreation_total_harm  hh_items_harm

foreach var of local list {
	rename valueCPI_`var' CPI_`var'
}


save temp_OECD.dta, replace



********************************************************************************
****************************EUROSTAT *******************************************
********************************************************************************
import delimited "original_data/prices/data_eurostat.csv", clear

rename time_period year
rename geo country 

drop dataflow freq unit unit obs_flag
rename obs_value value

sort country coicop year

replace coicop="CPI_all" if coicop=="CP00"
replace coicop="CPI_hh_items" if coicop=="CP05"
replace coicop="CPI_recreation_total" if coicop=="CP09"
replace coicop="CPI_recreation_audio_video" if coicop=="CP091"
replace coicop="CPI_recreation_books" if coicop=="CP095"
replace coicop="CPI_recreation_other_dur" if coicop=="CP092"
replace coicop="CPI_recreation_other_goods" if coicop=="CP093"
replace coicop="CPI_recreation_pack_hol" if coicop=="CP096"
replace coicop="CPI_recreation_services" if coicop=="CP094"

reshape wide value, i(country year) j(coicop) string

local list total audio_video books other_dur other_goods pack_hol services

foreach var of local list {
	rename valueCPI_recreation_`var' CPI_recreation_`var'_ES /*ES for EuroStat*/
}
rename valueCPI_all CPI_all_ES
rename valueCPI_hh_items CPI_hh_items_ES



rename country country_full
gen country=""

replace country="AUT" if country_full=="AT"
replace country="BEL" if country_full=="BE"
replace country="BGR" if country_full=="BG"
replace country="HRV" if country_full=="HR"
replace country="CYP" if country_full=="CY"
replace country="CZE" if country_full=="CZ"
replace country="DEU" if country_full=="DE"
replace country="DNK" if country_full=="DK"
replace country="EST" if country_full=="EE"
replace country="FIN" if country_full=="FI"
replace country="FRA" if country_full=="FR"
replace country="GRC" if country_full=="EL"
replace country="HUN" if country_full=="HU"
replace country="ISL" if country_full=="IS"
replace country="IRL" if country_full=="IE"
replace country="ITA" if country_full=="IT"
replace country="LVA" if country_full=="LV"
replace country="LTU" if country_full=="LT"
replace country="LUX" if country_full=="LU"
replace country="MKD" if country_full=="MK"
replace country="MLT" if country_full=="MT"
replace country="NLD" if country_full=="NL"
replace country="NOR" if country_full=="NO"
replace country="POL" if country_full=="PL"
replace country="PRT" if country_full=="PT"
replace country="ROU" if country_full=="RO"
replace country="SVK" if country_full=="SK"
replace country="SVN" if country_full=="SI"
replace country="SCG" if country_full=="RS"
replace country="ESP" if country_full=="ES"
replace country="SWE" if country_full=="SE"
replace country="CHE" if country_full=="CH"
replace country="TUR" if country_full=="TR"
replace country="GBR" if country_full=="UK"
replace country="USA" if country_full=="US"
drop if country==""

rename country_full country_short


save temp_eurostat_v2.dta, replace




********************************************************************************
******************************CANADA *******************************************
********************************************************************************
import delimited "original_data/prices/data_canada.csv", clear

rename ïref_date year
rename geo country
rename productsandproductgroups item
keep year country item value
label variable year "Year"


keep if item=="All-items" | item=="Recreation" | item=="Recreation, education and reading" | item=="Household operations, furnishings and equipment"

replace item="CPI_all" if item=="All-items"
replace item="CPI_recreation_total" if item=="Recreation" /*not including education and reading (incl recreational)*/
replace item="CPI_recreation_total_2" if item=="Recreation, education and reading"
replace item="CPI_hh_items" if item=="Household operations, furnishings and equipment"

reshape wide value, i(country year) j(item) string

rename valueCPI_all CPI_all_Can /*Can for Canada*/
rename valueCPI_recreation_total CPI_recreation_total_Can /*Can for Canada*/
rename valueCPI_recreation_total_2 CPI_recreation_total_2_Can /*Can for Canada*/
rename valueCPI_hh_items CPI_hh_items_Can /*Can for Canada*/

replace country="CAN"

save temp_canada_v2.dta, replace




********************************************************************************
******************************AUSTRALIA*****************************************
********************************************************************************

import excel "original_data/prices/data_australia.xls", sheet("Data1") cellrange(A11:AK299) clear


*see excel file for details
keep A J M F
rename A date
rename J CPI_recreation_total
rename M CPI_all
rename F CPI_hh_items



gen year=year(date)
sort year date
by year: egen CPI_recreation_total_Aus=mean(CPI_recreation_total)
by year: egen CPI_all_Aus=mean(CPI_all)
by year: egen CPI_hh_items_Aus=mean(CPI_hh_items)
by year: gen id=_n
keep if id==1
drop id date CPI_all CPI_recreation_total CPI_hh_items
keep if year>=1949
order year

gen country="AUS"

save temp_australia, replace





********************************************************************************
******************************USA***********************************************
********************************************************************************

use "..\US_prices_4_regions\prices_by_region.dta", clear
keep if region=="USA"
keep year price_total
rename price_total price_CPI

merge 1:1 year using "..\US_prices_4_regions\prices_US_rec_detailed.dta"

rename price_CPI CPI_all_USA
rename price_rec_weighted_r CPI_recreation_total_USA

keep CPI* year
gen country="USA"
order year country

save temp_USA, replace



********************************************************************************
******************************USA2**********************************************
********************************************************************************

use "..\US_long_series\US_long_price_series.dta", clear
gen country="USA"

rename price_rec_real CPI_recreation_real2

merge 1:1 year using "..\US_long_series\BLS, price\CPI_all_long_USA"
drop _merge
sort year
drop if CPI_recreation_real2==.
rename price_all CPI_all2

keep year country CPI_recreation_real2 CPI_all2

save temp_USA2, replace

********************************************************************************
*******************************MERGING******************************************
********************************************************************************

use temp_eurostat_v2, clear
merge 1:1 country year using temp_OECD
drop _merge

merge 1:1 country year using temp_canada_v2
drop _merge
*CPI_all and CPI_all_Can are very close

merge 1:1 country year using temp_australia
drop _merge


merge 1:1 country year using temp_USA
drop _merge

sort country year
keep if year<=2019


gen fin_CPI_all=.
gen fin_CPI_recreation=.
gen fin_CPI_hh_items=.



gen id_OECD=0
gen id_ES=0
replace id_OECD=1 if CPI_recreation_total!=.
replace id_ES=1 if CPI_recreation_total_ES!=.
by country: egen N_OECD=total(id_OECD)
by country: egen N_ES=total(id_ES)


replace fin_CPI_all=CPI_all if N_OECD>N_ES
replace fin_CPI_recreation=CPI_recreation_total if N_OECD>N_ES
replace fin_CPI_hh_items=CPI_hh_items if N_OECD>N_ES

replace fin_CPI_all=CPI_all_ES if N_OECD<=N_ES
replace fin_CPI_recreation=CPI_recreation_total_ES if N_OECD<=N_ES
replace fin_CPI_hh_items=CPI_hh_items_ES if N_OECD<=N_ES

replace fin_CPI_all=CPI_all_Aus if country=="AUS"
replace fin_CPI_recreation=CPI_recreation_total_Aus if country=="AUS"
replace fin_CPI_hh_items=CPI_hh_items_Aus if country=="AUS"

replace fin_CPI_all=CPI_all_Can if country=="CAN"
replace fin_CPI_recreation=CPI_recreation_total_Can if country=="CAN"
replace fin_CPI_hh_items=CPI_hh_items_Can if country=="CAN"

replace fin_CPI_all=CPI_all_USA if country=="USA"
replace fin_CPI_recreation=CPI_recreation_total_USA if country=="USA"
replace fin_CPI_hh_items=CPI_hh_items_ES if country=="USA" /*in detailed US data, there is no separate category, so use data form Eurostat/OECD*/



drop id_OECD id_ES N_OECD N_ES
gen id_OECD=0
gen id_ES=0
replace id_OECD=1 if CPI_all!=.
replace id_ES=1 if CPI_all_ES!=.
by country: egen N_OECD=total(id_OECD)
by country: egen N_ES=total(id_ES)
gen fin_CPI_all_longer=.
replace fin_CPI_all_longer=CPI_all if N_OECD>N_ES
replace fin_CPI_all_longer=CPI_all_ES if N_OECD<=N_ES
replace fin_CPI_all_longer=CPI_all_Aus if country=="AUS"
replace fin_CPI_all_longer=CPI_all_Can if country=="CAN"
replace fin_CPI_all_longer=CPI_all_USA if country=="USA"


keep year country fin*
*keep if fin_CPI_all!=.


gen fin_CPI_recreation_real=fin_CPI_recreation
gen fin_CPI_hh_items_real=fin_CPI_recreation
replace fin_CPI_hh_items_real=fin_CPI_hh_items/fin_CPI_all
replace fin_CPI_recreation_real=fin_CPI_recreation/fin_CPI_all if country!="USA" /*us recreation price is already real*/

merge 1:1 country year using temp_USA2
rename CPI_recreation_real2 fin_CPI_recreation_real2
drop _merge

gen byte tag=1 if year==2015
sort country tag
by country: gen CPI_recreation_real=fin_CPI_recreation_real/fin_CPI_recreation_real[1]
by country: gen CPI_recreation_real2=fin_CPI_recreation_real2/fin_CPI_recreation_real2[1]
by country: gen CPI_hh_items_real=fin_CPI_hh_items_real/fin_CPI_hh_items_real[1]

rename fin_CPI_all CPI_all
rename fin_CPI_recreation CPI_recreation_nominal
rename fin_CPI_all_longer CPI_all_longer

keep year country CPI_all CPI_recreation_real CPI_recreation_real2 CPI_all2 CPI_all_longer CPI_hh_items_real
sort country year




save prices_by_country, replace


erase temp_australia.dta
erase temp_OECD.dta
erase temp_eurostat_v2.dta
erase temp_canada_v2.dta
erase temp_USA.dta
erase temp_USA2.dta


